********************************************************************************
*
*	Paper:		Turning vaccines into vaccinations: Evidence from SSA
*	Program:	Write programs
*	Authors:	Markhof, Wollburg, Zezza
*	Last ed.:	20-07-2022
*
********************************************************************************

********************************************************************************
*	Set scheme styles
	set scheme white_tableau
	graph set window fontface "Arial Narrow"
********************************************************************************

********************************************************************************
*	Declare survey design
	svyset cluster [pw = hh_weight], strata(strata) singleunit(centered)
********************************************************************************


********************************************************************************
*	Program that writes tables for tabouts by country
********************************************************************************


capture program drop mytable_multvar
program define mytable_multvar
syntax varlist(numeric) , ccodes(numlist) lvl_num(integer) save(string) [across(varlist max=1) over(varlist max=2) title(string) colheader(string) note(string) no_overlap(integer 0)]

putexcel set "`save'", replace

local alphabet = "A B C D E F G H I J K L M N O P Q R S T U V W X Y Z"

local varcount : word count `varlist' 
 
local i = 0

foreach country of numlist `ccodes' {
	local i = `i'+1
	
	if `no_overlap' == 1 {
		foreach var of numlist 1/`: word count `varlist'' {
			capture svy: mean `: word `var' of `varlist'' if country1==`country', over(`over')
			
			if `var' == 1 {
				matrix table = r(table)
			}
			else {
				matrix table = table, r(table)
			}
		}
			
		if !_rc {
			local N`i' = .
			matrix means_`i' = table[1,1...]'
			matrix lb_`i' = table[5,1...]'
			matrix ub_`i' = table[6,1...]'
			
			matrix colnames means_`i' = "`: label country1 `country''"
		}
		
		else {
			local N`i' = .
			matrix means_`i' = J(`varcount', 1, .)
			matrix lb_`i' = J(`varcount', 1, .)
			matrix ub_`i' = J(`varcount', 1, .)
			
			matrix colnames means_`i' = "`: label country1 `country''"

		}
	
			
	}
	
	else {
		capture svy: mean `varlist' if country1==`country', over(`over')
	
		if "`over'" != "" { // use this loop if we're slicing the data further by the variable specified in `over'
			
			if !_rc { // use this loop if the variable(s) is/are not missing
				
				matrix means_`i' = r(table)[1,1..2]
				matrix lb_`i' = r(table)[5,1..2]
				matrix ub_`i' = r(table)[6,1..2]
				
				foreach x of numlist 3(2)`=`varcount'*2-1' {
					matrix means_`i' = means_`i' \ r(table)[1,`x'..`=`x'+1']
					matrix lb_`i' = lb_`i' \ r(table)[5,`x'..`=`x'+1']
					matrix ub_`i' = ub_`i' \ r(table)[6,`x'..`=`x'+1']
				}

				matrix colnames means_`i' = "`: label `over' 0'" "`: label `over' 1'"
				
				count if !mi(`: word 1 of `varlist'') & `over'==0 & country1==`country'
				local N`i'_0 = r(N)
				
				count if !mi(`: word 1 of `varlist'') & `over'==1 & country1==`country'
				local N`i'_1 = r(N)				
			}
			
			else { // use this loop to create an empty matrix in case the variable(s) is/are missing

				local N`i'_0 = .			
				local N`i'_1 = .

				matrix means_`i' = J(`varcount', 2, .)
				matrix lb_`i' = J(`varcount', 2, .)
				matrix ub_`i' = J(`varcount', 2, .)
				
				matrix colnames means_`i' = "`: label `over' 0'" "`: label `over' 1'"
			}
		}
		
		
		else { // use this loop if we're not further slicing the data
			if !_rc {
				local N`i' = e(N)
				matrix means_`i' = r(table)[1,1...]'
				matrix lb_`i' = r(table)[5,1...]'
				matrix ub_`i' = r(table)[6,1...]'
				
				matrix colnames means_`i' = "`: label country1 `country''"
			}
			
			else {
				local N`i' = .
				matrix means_`i' = J(`varcount', 1, .)
				matrix lb_`i' = J(`varcount', 1, .)
				matrix ub_`i' = J(`varcount', 1, .)
				
				matrix colnames means_`i' = "`: label country1 `country''"
			}
		}
	
	
	}
	
	mata {		// this bit makes sure there are no negative lower boundaries for CIs
		lb_`i' = st_matrix("lb_`i'")
		lb_`i' = (lb_`i' :>= 0) :* lb_`i'
		st_matrix("lb_`i'", lb_`i')
	}
	
}	

foreach j of numlist 1/`lvl_num' { // merge the matrices into one matrix for the point estimate, one for the upper CI and one for the lower CI
		
		if "`over'" != "" { // If we slice the data by the category specified in `over', use this loop to accommodate the extra columns
		
			if `j' == 1 { // first time
				matrix N	 = `N1_0', `N1_1'
				matrix means = means_1
				matrix lb	 = lb_1
				matrix ub	 = ub_1
			}
			if `j' > 1 { // subsequent times
				matrix N	 = N , `N`j'_0' , `N`j'_1'
				matrix means = means , means_`j'
				matrix lb	 = lb , lb_`j'
				matrix ub	 = ub , ub_`j'

			}
		}
	
	else { // If we don't slice the data further, use this loop 
			if `j' == 1 { // first time
				matrix N	 = `N1'
				matrix means = means_1
				matrix lb	 = lb_1
				matrix ub	 = ub_1
		}
		
			if `j' > 1 { // subsequent times
				matrix N	 = N , `N`j''
				matrix means = means , means_`j'
				matrix lb	 = lb , lb_`j'
				matrix ub	 = ub , ub_`j'
		}
	}
	
	
}
	
	
	
matrix means = means * 100 // express means in percent
	
foreach row of numlist 1/`varcount' {
	foreach col of numlist 1/`lvl_num' {
		matrix means[`row', `col'] = round(means[`row',`col'], 0.1)
	}
}

{	/* This part labels the matrix rows with the variable labels*/
	if `varcount' == 1 {
		matrix rownames means = "`: variable label `: word 1 of `varlist'''" 
	}
	if `varcount' == 2 {
		matrix rownames means = "`: variable label `: word 1 of `varlist'''" "`: variable label `: word 2 of `varlist'''"
	}
	if `varcount' == 3 {
		matrix rownames means = "`: variable label `: word 1 of `varlist'''" "`: variable label `: word 2 of `varlist'''" "`: variable label `: word 3 of `varlist'''"
	}
	if `varcount' == 4 {
		matrix rownames means = "`: variable label `: word 1 of `varlist'''" "`: variable label `: word 2 of `varlist'''" "`: variable label `: word 3 of `varlist'''" "`: variable label `: word 4 of `varlist'''"
	}
	if `varcount' == 5 {
		matrix rownames means = "`: variable label `: word 1 of `varlist'''" "`: variable label `: word 2 of `varlist'''" "`: variable label `: word 3 of `varlist'''" "`: variable label `: word 4 of `varlist'''" ///
			"`: variable label `: word 5 of `varlist'''"
	}
	if `varcount' == 6 {
		matrix rownames means = "`: variable label `: word 1 of `varlist'''" "`: variable label `: word 2 of `varlist'''" "`: variable label `: word 3 of `varlist'''" "`: variable label `: word 4 of `varlist'''" ///
			"`: variable label `: word 5 of `varlist'''" "`: variable label `: word 6 of `varlist'''"
	}
	if `varcount' == 7 {
		matrix rownames means = "`: variable label `: word 1 of `varlist'''" "`: variable label `: word 2 of `varlist'''" "`: variable label `: word 3 of `varlist'''" "`: variable label `: word 4 of `varlist'''" ///
			"`: variable label `: word 5 of `varlist'''" "`: variable label `: word 6 of `varlist'''" "`: variable label `: word 7 of `varlist'''"
	}
	if `varcount' == 8 {
		matrix rownames means = "`: variable label `: word 1 of `varlist'''" "`: variable label `: word 2 of `varlist'''" "`: variable label `: word 3 of `varlist'''" "`: variable label `: word 4 of `varlist'''" ///
			"`: variable label `: word 5 of `varlist'''" "`: variable label `: word 6 of `varlist'''" "`: variable label `: word 7 of `varlist'''" "`: variable label `: word 8 of `varlist'''"
	}
	if `varcount' == 9 {
		matrix rownames means = "`: variable label `: word 1 of `varlist'''" "`: variable label `: word 2 of `varlist'''" "`: variable label `: word 3 of `varlist'''" "`: variable label `: word 4 of `varlist'''" ///
			"`: variable label `: word 5 of `varlist'''" "`: variable label `: word 6 of `varlist'''" "`: variable label `: word 7 of `varlist'''" "`: variable label `: word 8 of `varlist'''" ///
			"`: variable label `: word 9 of `varlist'''"
	}
	if `varcount' == 10 {
		matrix rownames means = "`: variable label `: word 1 of `varlist'''" "`: variable label `: word 2 of `varlist'''" "`: variable label `: word 3 of `varlist'''" "`: variable label `: word 4 of `varlist'''" ///
			"`: variable label `: word 5 of `varlist'''" "`: variable label `: word 6 of `varlist'''" "`: variable label `: word 7 of `varlist'''" "`: variable label `: word 8 of `varlist'''" ///
			"`: variable label `: word 9 of `varlist'''" "`: variable label `: word 10 of `varlist'''"
	}
	if `varcount' == 11 {
		matrix rownames means = "`: variable label `: word 1 of `varlist'''" "`: variable label `: word 2 of `varlist'''" "`: variable label `: word 3 of `varlist'''" "`: variable label `: word 4 of `varlist'''" ///
			"`: variable label `: word 5 of `varlist'''" "`: variable label `: word 6 of `varlist'''" "`: variable label `: word 7 of `varlist'''" "`: variable label `: word 8 of `varlist'''" ///
			"`: variable label `: word 9 of `varlist'''" "`: variable label `: word 10 of `varlist'''" "`: variable label `: word 11 of `varlist'''"
	}
	if `varcount' == 12 {
		matrix rownames means = "`: variable label `: word 1 of `varlist'''" "`: variable label `: word 2 of `varlist'''" "`: variable label `: word 3 of `varlist'''" "`: variable label `: word 4 of `varlist'''" ///
			"`: variable label `: word 5 of `varlist'''" "`: variable label `: word 6 of `varlist'''" "`: variable label `: word 7 of `varlist'''" "`: variable label `: word 8 of `varlist'''" ///
			"`: variable label `: word 9 of `varlist'''" "`: variable label `: word 10 of `varlist'''" "`: variable label `: word 11 of `varlist'''" "`: variable label `: word 12 of `varlist'''"
			
	}
	if `varcount' == 13 {
		matrix rownames means = "`: variable label `: word 1 of `varlist'''" "`: variable label `: word 2 of `varlist'''" "`: variable label `: word 3 of `varlist'''" "`: variable label `: word 4 of `varlist'''" ///
			"`: variable label `: word 5 of `varlist'''" "`: variable label `: word 6 of `varlist'''" "`: variable label `: word 7 of `varlist'''" "`: variable label `: word 8 of `varlist'''" ///
			"`: variable label `: word 9 of `varlist'''" "`: variable label `: word 10 of `varlist'''" "`: variable label `: word 11 of `varlist'''" "`: variable label `: word 12 of `varlist'''" ///
			"`: variable label `: word 13 of `varlist'''"
	}
	if `varcount' == 14 {
		matrix rownames means = "`: variable label `: word 1 of `varlist'''" "`: variable label `: word 2 of `varlist'''" "`: variable label `: word 3 of `varlist'''" "`: variable label `: word 4 of `varlist'''" ///
			"`: variable label `: word 5 of `varlist'''" "`: variable label `: word 6 of `varlist'''" "`: variable label `: word 7 of `varlist'''" "`: variable label `: word 8 of `varlist'''" ///
			"`: variable label `: word 9 of `varlist'''" "`: variable label `: word 10 of `varlist'''" "`: variable label `: word 11 of `varlist'''" "`: variable label `: word 12 of `varlist'''" ///
			"`: variable label `: word 13 of `varlist'''" "`: variable label `: word 14 of `varlist'''"
	}
	if `varcount' == 15 {
		matrix rownames means = "`: variable label `: word 1 of `varlist'''" "`: variable label `: word 2 of `varlist'''" "`: variable label `: word 3 of `varlist'''" "`: variable label `: word 4 of `varlist'''" ///
			"`: variable label `: word 5 of `varlist'''" "`: variable label `: word 6 of `varlist'''" "`: variable label `: word 7 of `varlist'''" "`: variable label `: word 8 of `varlist'''" ///
			"`: variable label `: word 9 of `varlist'''" "`: variable label `: word 10 of `varlist'''" "`: variable label `: word 11 of `varlist'''" "`: variable label `: word 12 of `varlist'''" ///
			"`: variable label `: word 13 of `varlist'''" "`: variable label `: word 14 of `varlist'''" "`: variable label `: word 15 of `varlist'''"
	}	
	
	if `varcount' == 16 {
	matrix rownames means = "`: variable label `: word 1 of `varlist'''" "`: variable label `: word 2 of `varlist'''" "`: variable label `: word 3 of `varlist'''" "`: variable label `: word 4 of `varlist'''" ///
		"`: variable label `: word 5 of `varlist'''" "`: variable label `: word 6 of `varlist'''" "`: variable label `: word 7 of `varlist'''" "`: variable label `: word 8 of `varlist'''" ///
		"`: variable label `: word 9 of `varlist'''" "`: variable label `: word 10 of `varlist'''" "`: variable label `: word 11 of `varlist'''" "`: variable label `: word 12 of `varlist'''" ///
		"`: variable label `: word 13 of `varlist'''" "`: variable label `: word 14 of `varlist'''" "`: variable label `: word 15 of `varlist'''" "`: variable label `: word 16 of `varlist'''"
	}	

	if `varcount' == 17 {
	matrix rownames means = "`: variable label `: word 1 of `varlist'''" "`: variable label `: word 2 of `varlist'''" "`: variable label `: word 3 of `varlist'''" "`: variable label `: word 4 of `varlist'''" ///
		"`: variable label `: word 5 of `varlist'''" "`: variable label `: word 6 of `varlist'''" "`: variable label `: word 7 of `varlist'''" "`: variable label `: word 8 of `varlist'''" ///
		"`: variable label `: word 9 of `varlist'''" "`: variable label `: word 10 of `varlist'''" "`: variable label `: word 11 of `varlist'''" "`: variable label `: word 12 of `varlist'''" ///
		"`: variable label `: word 13 of `varlist'''" "`: variable label `: word 14 of `varlist'''" "`: variable label `: word 15 of `varlist'''" "`: variable label `: word 16 of `varlist'''" ///
		"`: variable label `: word 17 of `varlist'''"
	}		
	
}	
	

	
if "`over'" != "" { // use this loop if we are slicing the data further by the variable in `over'
	foreach z of numlist 1/`=`lvl_num'*2' { // we need twice as many columns as countries (each country by each category in `over')
		local i = 0
		
		foreach x of numlist 4(2)`= `varcount' *2 + 2' {
			local i = `i'+1
			
			local k : display %2.1f lb[`i',`z']*100
			local l : display %2.1f ub[`i',`z']*100
			
			
		if `k' != . {
			local m = "(`k' to `l')"
		}
		
		if `k' == . {
			local m = " "
		}
			
			if `i' == 1 & `z' == 1 {
				putexcel `: word `z' of `alphabet''`x' = matrix(means["`: variable label `: word `i' of `varlist'''",`z']), names nformat(#0.0) hcenter
				putexcel `: word `=`z'+1' of `alphabet''`=`x'+2' = "`m'", hcenter
			}
			
			if `i' == 1 & `z' > 1 {
				putexcel `: word `=`z'+1' of `alphabet''`x' = matrix(means["`: variable label `: word `i' of `varlist'''",`z']), colnames nformat(#0.0) hcenter
				putexcel `: word `=`z'+1' of `alphabet''`=`x'+2' = "`m'", hcenter
			}
			
			
			if `i' > 1 & `z' == 1 {
				putexcel `: word `z' of `alphabet''`=`x'+1' = matrix(means["`: variable label `: word `i' of `varlist'''",`z']), rownames nformat(#0.0) hcenter
				putexcel `: word `=`z'+1' of `alphabet''`=`x'+2' = "`m'", hcenter
				
			}
			
			if `i' > 1 & `z' > 1 {
				putexcel `: word `=`z'+1' of `alphabet''`=`x'+1' = matrix(means["`: variable label `: word `i' of `varlist'''",`z']), nformat(#0.0) hcenter
				putexcel `: word `=`z'+1' of `alphabet''`=`x'+2' = "`m'", hcenter
				
			}
			
		}
	}		
			
	putexcel A1:`: word `=`lvl_num'*2+1' of `alphabet''`=`varcount'*2+7'	, font("Times New Roman", 8) fpattern(solid, white)
	putexcel A2:`: word `=`lvl_num'*2+1' of `alphabet''2 = "`title'", hcenter bold font("Times New Roman", 10) merge border(bottom)
	putexcel A3:A4 = "`colheader'", left bold font("Times New Roman", 8) merge vcenter
	putexcel A4:A`=`varcount'*2+5' , left
	putexcel A4:`: word `=`lvl_num'*2+1' of `alphabet''4 , border(bottom, double) bold
	putexcel A`=`varcount'*2+5' = "N", italic border(top)
	putexcel B`=`varcount'*2+5' = matrix(N), hcenter nformat(#,###) border(top)
	putexcel A`=`varcount'*2+6':`: word `=`lvl_num'*2+1' of `alphabet''`=`varcount'*2+6' = "`note'" , font("Times New Roman", 10) border(top, medium) merge txtwrap
	
	local codelist = "`ccodes'"
	local j = 0
	foreach col of numlist 2(2)`=`lvl_num'*2' {
		local j = `j'+1
		putexcel `: word `col' of `alphabet''3:`: word `=`col'+1' of `alphabet''3 = "`: label country1 `: word `j' of `codelist'''" , merge hcenter bold
		
		putexcel `: word `=`col'+1' of `alphabet''3:`: word `=`col'+1' of `alphabet''`=`varcount'*2+5' , border(right, dotted)
	}
	
	
	
}	
	
else { // use this loop if we are not slicing the data further
	foreach z of numlist 1/`lvl_num' {
		local i = 0
		
		foreach x of numlist 4(2)`= `varcount' *2 + 2' {
			local i = `i'+1
			
			local k : display %2.1f lb[`i',`z']*100
			local l : display %2.1f ub[`i',`z']*100
			
		if `k' != . {
			local m = "(`k' to `l')"
		}
		
		if `k' == . {
			local m = " "
		}
			
			if `i' == 1 & `z' == 1 {
				putexcel `: word `z' of `alphabet''`x' = matrix(means["`: variable label `: word `i' of `varlist'''",`z']), names nformat(#0.0) hcenter
				putexcel `: word `=`z'+1' of `alphabet''`=`x'+2' = "`m'", hcenter
			}
			
			if `i' == 1 & `z' > 1 {
				putexcel `: word `=`z'+1' of `alphabet''`x' = matrix(means["`: variable label `: word `i' of `varlist'''",`z']), colnames nformat(#0.0) hcenter
				putexcel `: word `=`z'+1' of `alphabet''`=`x'+2' = "`m'", hcenter
			}
			
			
			if `i' > 1 & `z' == 1 {
				putexcel `: word `z' of `alphabet''`=`x'+1' = matrix(means["`: variable label `: word `i' of `varlist'''",`z']), rownames nformat(#0.0) hcenter
				putexcel `: word `=`z'+1' of `alphabet''`=`x'+2' = "`m'", hcenter
				
			}
			
			if `i' > 1 & `z' > 1 {
				putexcel `: word `=`z'+1' of `alphabet''`=`x'+1' = matrix(means["`: variable label `: word `i' of `varlist'''",`z']), nformat(#0.0) hcenter
				putexcel `: word `=`z'+1' of `alphabet''`=`x'+2' = "`m'", hcenter
				
			}
			
		}
	}		
			
	putexcel A1:`: word `=`lvl_num'+1' of `alphabet''`=`varcount'*2+6'	, font("Times New Roman", 8) fpattern(solid, white)
	putexcel A3:`: word `=`lvl_num'+1' of `alphabet''3 = "`title'", hcenter bold font("Times New Roman", 10) merge border(bottom)
	putexcel A4 = "`colheader'", left bold font("Times New Roman", 8)
	putexcel A4:A`=`varcount'*2+5' , left
	putexcel A4:`: word `=`lvl_num'+1' of `alphabet''4 , border(bottom, double)
	putexcel A`=`varcount'*2+5' = "N", italic border(top)
	putexcel B`=`varcount'*2+5' = matrix(N), hcenter nformat(#,###) border(top)
	putexcel A`=`varcount'*2+6':`: word `=`lvl_num'+1' of `alphabet''`=`varcount'*2+6' = "`note'" , font("Times New Roman", 10) border(top, medium) merge txtwrap
}	
	
	
	
end	
	

	



* Write program that creates waffle charts
capture program drop waffle2
program define waffle2
syntax varname(numeric) [fweight aweight pweight iweight] [if] [, ROWs(integer 1) COLs(integer 1) by(varname) title(string)]

preserve
	collapse (mean) `varlist' [`weight' `exp'] `if' , by(`by')
	local r 	= `rows'
	local c 	= `cols'
	local obs	= `c'*`r'
	
	expand `obs'
	bys `by': 	egen y = seq(), b(`c')
				egen x = seq(), t(`c')
					
	bys `by': 	gen id 	= _n
				egen tag 	= tag(`by')
				gen dots	= .
	
	levelsof `by', local(levels)
	
	foreach x of local levels {
		qui{
		summ `varlist' if `by'=="`x'" & tag==1
		local `varlist'_loc	= `r(mean)'
		
		summ id if `by'=="`x'"
		replace dots = id <= int(``varlist'_loc' * `r(max)') if `by'=="`x'"
		}
	}
	

	cap drop country2
	gen `by'2 = `by' + " (" + string(`varlist' * 100, "%9.1f") + "%)"  
	// final graph  
	twoway ///
	 (scatter y x if dots==0, msize(0.8) msymbol(square) mc(gs14)) ///
	 (scatter y x if dots==1, msize(0.8) msymbol(square)) ///
	  , ///
	  ytitle("") yscale(noline) ylabel(, nogrid) ///
	  xtitle("") xscale(noline) xlabel(, nogrid) ///
	  by(, title("{fontface Merriweather Bold:`title'}", margin(medlarge)) ///
	  note("", size(vsmall))) ///
	  by(, legend(off)) ///
	  by(`by'2, /*rows(2)*/ noiyaxes noixaxes noiytick noixtick noiylabel noixlabel) ///
	  subtitle(, size(3) nobox) ///
	  aspectratio(1)
	
restore

end	



